import pandas as pd
import csv
df = pd.read_csv(r'../original/KING_audit.tab',delimiter = '\t').reset_index()
#pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)


#remove the first row
df.drop(index=df.index[0], axis=0,  inplace=True)

#replace commas

#df.to_csv (r'../ready/King_audit.csv')
df = df.replace(',','', regex=True)

#Remove the extraneous columns

df.drop(df.index[6], axis=0, inplace = True)
df.drop(df.index[17], axis=0, inplace = True)
df.drop(df.index[24], axis=0, inplace = True)
df.drop(df.index[31], axis=0, inplace = True)
df.drop(df.index[38], axis=0, inplace = True)
df.drop(df.index[45], axis=0, inplace = True)
df.drop(df.index[52], axis=0, inplace = True)
df.drop(df.index[59], axis=0, inplace = True)
df.drop(df.index[66], axis=0, inplace = True)
df = df.reset_index()

#create and write to columns for state, county date, should all be the same for this file

df["state"] = "WASHINGTON"
df["county"] = "KING"
df["date"] = "2020-11-03"

df["office"] = ""


#add in what precint that they are representing

df["precinct"] = ""

#add in who the candidate is

df["candidate"] = df["level_0"]

#add in the party

df["party"] = ""

#add in the original votes
df["original_votes"] = df["level_1"]

#add in the audited votes
df["audited_votes"] = df["Legislative District No. 5 State Senator"]

#Remove first 3 columns because they are not required

df.drop(["level_0", "level_1","Legislative District No. 5 State Senator","index"], axis = 1, inplace = True)

#Remove the orginal and recount results

df.drop(df.index[6], axis=0, inplace = True)
df.drop(df.index[16], axis=0, inplace = True)
df.drop(df.index[22], axis=0, inplace = True)
df.drop(df.index[22], axis=0, inplace = True)
df.drop(df.index[27], axis=0, inplace = True)
df.drop(df.index[33], axis=0, inplace = True)
df.drop(df.index[39], axis=0, inplace = True)
df.drop(df.index[45], axis=0, inplace = True)
df.drop(df.index[51], axis=0, inplace = True)
df.drop(df.index[57], axis=0, inplace = True)

df = df.reset_index()

df.drop(["index"], axis = 1, inplace = True)

#convert original and audited votes from strings to int

df[["original_votes", "audited_votes"]] = df[["original_votes", "audited_votes"]].apply(pd.to_numeric)

#calculate the final difference between the two

df['diff'] = df["original_votes"] - df["audited_votes"]

#Requested vs. Mandatory

df["recount_type"] = ""





#add in row that got deleted

df.loc[21.5] = ['WASHINGTON', 'KING', '2020-11-03', 'US HOUSE', 'FED 30-3021 PRECINCT', 'BALLOTS CAST', 'NONPARTISAN', '602', '602', '0', 'REQUESTED' ]
df = df.sort_index().reset_index(drop=True)

#Remove the VP and replace with just the Pres in candidate role 


df.loc[1, 'candidate'] = 'Ingrid Anderson'
df.loc[2, 'candidate'] = 'Mark Mullet'

df.loc[7, 'candidate'] = 'JOSEPH R BIDEN'
df.loc[8, 'candidate'] = 'DONALD J TRUMP'
df.loc[9, 'candidate'] = 'Jo Jorgensen'
df.loc[10, 'candidate'] = 'Howie Hawkins'
df.loc[11, 'candidate'] = 'Gloria LaRiva'
df.loc[12, 'candidate'] = 'Alyson Kennedy'
df.loc[17, 'candidate'] = 'Suzan DelBene'
df.loc[18, 'candidate'] = 'Jeffrey Beeler'
df.loc[[23,29,35,41,47], 'candidate'] = 'Adam Smith'
df.loc[[24,30,36,42,48], 'candidate'] = 'Doug basler'
df.loc[53, 'candidate'] = 'Jay Inslee'
df.loc[54, 'candidate'] = 'Loren Culp'
df.loc[59, 'candidate'] = 'Bob FERGUSON'
df.loc[60, 'candidate'] = 'Matt larkin'
 

#zdd in political parties

#First write over everything as non-partisan

df.loc[1:2, 'party'] = "Democrat"
df.loc[7, 'party'] = "Democrat"
df.loc[8,'party'] = "Republican"
df.loc[9, 'party'] = 'libertarian'
df.loc[10, 'party'] = 'green'
df.loc[11, 'party'] = 'Party for Socialism and Liberation'
df.loc [12, 'party'] = 'Socialist Workers Party'
df.loc[17, 'party'] = 'Democrat'
df.loc[18, 'party'] = 'Republican'
df.loc[[23,29,35,41,47], 'party'] = 'Democrat'
df.loc[[24,30,36,42,48], 'party'] = 'Republican'
df.loc[53, 'party'] = 'Democrat'
df.loc[54, 'party'] = 'Republican'
df.loc[59, 'party'] = 'Democrat'
df.loc[60, 'party'] = 'Republican'




#Then add in the other stuff


#add in what office that people are running for

df.loc[0:5, 'office'] = "STATE SENATE"
df.loc[6:15, 'office'] = "US PRESIDENT"
df.loc[16:50, 'office'] = "US HOUSE"
df.loc[51:56, 'office'] = "GOVERNOR"
df.loc[57:63, 'office'] = "ATTORNEY GENERAL"

#add in the precienct that people are running for 

df.loc[0:5, 'precinct'] = "LEGISLATIVE DISTRICT NO. 5"
df.loc[6:15, 'precinct'] = "ROSE PRECINCT"
df.loc[16:27, 'precinct'] = "FED 30-3021 PRECINCT"
df.loc[28:33, 'precinct'] = "KEN 47-0842 PRECINCT"
df.loc[34:39, 'precinct'] = "KEN 47-2568 PRECINCT"
df.loc[40:45, 'precinct'] = "MI 41-0775 PRECINCT"
df.loc[46:63, 'precinct'] = "SEA 37-1895 PRECINCT"

#add in requested or manual recount

df.loc[0:15, 'recount_type'] = "MANDATORY"
df.loc[16:63, 'recount_type'] = "REQUESTED"

#make the candiate columns all caps

df['candidate'] = df['candidate'].str.upper()
df['party'] = df['party'].str.upper()

#print(df)

df.to_csv("../ready/king_cleaned.csv", index=False)
